﻿CREATE PROCEDURE [dbo].[spLoadCustomer]
	@CustomerID int
AS
	declare @Person table ( PersonID int )
	insert into @Person
	select PersonID from tblCustomer 
	where CustomerID = @CustomerID
	union select ShipTo_PersonID from tblOrderHeader 
	where CustomerID = @CustomerID

	declare @Address table ( AddressID int )
	insert into @Address
	select T1.AddressID from tblPerson T1
		join @Person T2 on T1.PersonID = T2.PersonID
	union select T1.ShipTo_AddressID from tblOrderHeader T1
	where T1.CustomerID = @CustomerID

	-- Load codeCountry
	select distinct T1.* from codeCountry T1
		join tblAddress T2 on T1.CountryID = T2.CountryID
		join @Address T3 on T2.AddressID = T3.AddressID

	-- Load tblAddress
	select T1.* from tblAddress T1
		join @Address T2 on T1.AddressID = T2.AddressID

	-- Load tblPerson
	select T1.* from tblPerson T1
		join @Person T2 on T1.PersonID = T2.PersonID

	-- Load the contactPointType
	select distinct T1.* from codeContactPointType T1
		join tblContactPoint T2 on T1.ContactPointTypeCode = T2.ContactPointTypeCode
		join @Person T3 on T2.PersonID = T3.PersonID

	-- Load tblCustomer
	select * from tblCustomer where CustomerID = @CustomerID

	-- Load tblContactPoint
	select distinct T1.* from tblContactPoint T1
		join @Person T2 on T1.PersonID = T2.PersonID

	-- Load codeHoldReason
	select distinct T1.* from codeHoldReason T1
		join tblOrderHeader T2 on T1.HoldReasonCode = T2.HoldReasonCode
	where T2.CustomerID = @CustomerID

	-- Load codeOrderSource
	select distinct T1.* from codeOrderSource T1
		join tblOrderHeader T2 on T1.OrderSourceCode = T2.OrderSourceCode
	where T2.CustomerID = @CustomerID

	-- Load tblPromotion
	select distinct T1.* from tblPromotion T1
		join tblOrderHeader T2 on T1.PromotionID = T2.PromotionID
	where T2.CustomerID = @CustomerID

	-- Load codeOrderType
	select distinct T1.* from codeOrderType T1
		join tblOrderHeader T2 on T1.OrderTypeCode = T2.OrderTypeCode
	where T2.CustomerID = @CustomerID

	-- Load codeOrderStatus
	select distinct T1.* from codeOrderStatus T1
		join tblOrderHeader T2 on T1.OrderStatusCode = T2.OrderStatusCode
	where T2.CustomerID = @CustomerID

	-- Load tblOrderHEader
	select * from tblOrderHeader
	where CustomerID = @CustomerID

	-- Load tblProduct
	select T1.* from tblProduct T1
		join tblOrderDetail T2 on T1.ProductID = T2.ProductID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID  = @CustomerID
	union select T1.* from tblProduct T1
		join tblSequenceProduct T2 on T1.ProductID = T2.ProductID
		join tblSequenceOrder T3 on T2.SequenceID = T3.SequenceID
		join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	where T4.CustomerID  = @CustomerID
	union select T1.* from tblProduct T1
		join tblSequenceStepProduct T2 on T1.ProductID = T2.ProductID
		join tblSequenceStep T3 on T2.SequenceStepID = T3.SequenceStepID
		join tblSequenceOrder T4 on T3.SequenceOrderID = T4.SequenceOrderID
		join tblOrderHeader T5 on T4.OrderID = T5.OrderID
	where T5.CustomerID  = @CustomerID

	-- Load tblOrderDetail
	select T1.* from tblOrderDetail T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblSequence
	select distinct T1.* from tblSequence T1
		join tblSequenceStep T2 on T1.SequenceID = T2.SequenceID
		join tblSequenceOrder T3 on T2.SequenceOrderID = T3.SequenceOrderID
		join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	where T4.CustomerID = @CustomerID

	-- Load tblSequenceOrder
	select distinct T1.* from tblSequenceOrder T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblSequenceStep
	select T1.* from tblSequenceStep T1
		join tblSequenceOrder T2 on T1.SequenceOrderID = T2.SequenceOrderID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblRefund
	select T1.* from tblRefund T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblRMAReturn
	select T1.* from tblRMAReturn T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblReturnDetail
	select T1.* from tblReturnDetail T1
		join tblRMAReturn T2 on T1.RMAReturnID = T2.RMAReturnID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load codeTransactionStatus
	select * from codeTransactionStatus

	-- Load codeTransactionType
	select * from codeTransactionType

	-- Load tblTransaction
	select T1.* from tblTransaction T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
	where T2.CustomerID = @CustomerID

	-- Load tblAccount
	select T1.* from tblAccount T1
		join tblTransactionDetail T2 on T1.AccountID = T2.AccountID
		join tblTransaction T3 on T2.TransactionID = T3.TransactionID
		join tblOrderHeader T4 on T3.OrderID = T4.OrderID
	where T4.CustomerID = @CustomerID

	-- Load tblTransactionDetail
	select T1.* from tblTransactionDetail T1
		join tblTransaction T2 on T1.TransactionID = T2.TransactionID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.CustomerID = @CustomerID

	-- Load tblMiscDef
	select T1.* from tblMiscDef T1
	where OwnerID = null 
		or OwnerID = ( select OwnerID from tblCustomer where CustomerID = @CustomerID )
